package com.onlineclass.dao;

import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.onlineclass.bean.Course;
import com.onlineclass.util.JDBCUtils;

/**
 * 课程DAO
 * 
 * @author david
 *
 */
public class CourseDAO {
	static QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

	/**
	 * 根据所属子类别查询指定课程
	 * 
	 * @param topId
	 * @return
	 */
	public List<Course> list(List<Integer> ids, Integer isPay, Integer order) {
		try {
			// 基础SQL
			StringBuffer sql = new StringBuffer("select * from t_course where 1=1 ");

			// 如果传入id
			if (ids != null) {
				if (ids.size() > 0) {
					sql.append(" and tagId in (");
					for (Integer id : ids) {
						sql.append(id + ",");
					}
					sql = new StringBuffer(sql.substring(0, sql.length() - 1));
					sql.append(")");
				}
			}

			// 如果传入付费条件
			if (isPay != null) {
				sql.append(" and isPay=" + isPay);
			}

			// 如果传入排序条件
			if (order != null) {
				String orderBy = (order == 0) ? "created" : "hots";
				sql.append(" order by " + orderBy + " desc");
			}

			List<Course> list = qr.query(sql.toString(),
					new BeanListHandler<Course>(Course.class));
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 模糊搜索
	 * @param kw
	 * @return
	 */
	public List<Course> search(String kw) {
		List<Course> list = null;
		try {
			String sql = "select * from t_course where title like concat('%',?,'%')";
			list = qr.query(sql,
					new BeanListHandler<Course>(Course.class), kw);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;
	}

	/**
	 * 根据id查询单个课程
	 * 
	 * @param id
	 * @return
	 */
	public Course findById(Integer id) {
		Course course = null;
		try {
			course = qr.query("select * from t_course where id=?",
					new BeanHandler<Course>(Course.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return course;
	}

	/**
	 * 更改某个课程的人气值
	 * 
	 * @param id
	 */
	public void updateHits(Integer id) {
		try {
			qr.update("update t_course set hots=hots+1 where id=?", id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据课程id查询相关课程：查同一个标签下的课程，查5个
	 * 
	 * @param id
	 * @return
	 */
	public List<Course> relatedCourse(Integer id) {
		List<Course> list = null;

		try {
			list = qr.query(
					"select * from t_course where tagId in(select tagId from t_course where id=?) limit 5",
					new BeanListHandler<Course>(Course.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public static void main(String[] args) {

	}
}
